﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;

namespace CNative.Dapper.Utils
{
    internal class OracleProvider : BaseProvider
    {
        public OracleProvider(IDbHelper _db) : base(_db)
        {
        }
        #region 关键字
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public override string ProviderName
        {
            get
            {
                //return "System.Data.OracleClient";//依赖于oracle官方驱动，需要另外安装oracle客户端
                //return "Oracle.DataAccess.Client";//Oracle数据库，官方非托管驱动，限制比较多
                return "Oracle.ManagedDataAccess.Client";//Oracle官方托管驱动,10g以下版本不支持，无任何依赖
            }
        }
        public override string ProviderNameFactory
        {
            get
            {
                return "Oracle.ManagedDataAccess.Client.OracleClientFactory";
            }
        }
        //public override string ProviderNameDll
        //{
        //    get
        //    {
        //        //return "System.Data.OracleClient.dll";//依赖于oracle官方驱动，需要另外安装oracle客户端
        //        //return "Oracle.DataAccess.dll";//Oracle数据库，官方非托管驱动，限制比较多
        //        return "Oracle.ManagedDataAccess.dll";//Oracle官方托管驱动,10g以下版本不支持，无任何依赖
        //    }
        //}
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override DatabaseType DBType { get { return DatabaseType.Oracle; } }
        /// <summary>
        /// 参数关键字 ?
        /// </summary>
        public override string ParamKeyword
        {
            get
            {
                return ":";
            }
        }
        /// <summary>
        /// 关键字前缀 `
        /// </summary>
        public override string SuffixLeft
        {
            get
            {
                return "\"";
            }
        }
        /// <summary>
        /// 关键字后缀 `
        /// </summary>
        public override string SuffixRigh
        {
            get
            {
                return "\"";
            }
        }
        #endregion

        #region DbProviderFactory
        public override string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? tb.TableName : tb.Schema + "." + tb.TableName;
        }
        #endregion

        #region GetDbParameter
        ///// <summary>
        ///// 获取DbParameter
        ///// </summary>
        ///// <param name="dbType">数据库类型</param>
        ///// <returns></returns>
        //public override IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
        //    DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        //{
        //    IDataParameter para = null;
        //    //--------------------------------------------------------------------------------------------------------------
        //    var frets = FillerParameter(propName, val, paramSuffix);
        //    if (frets.Item1)
        //    {
        //        return para;
        //    }
        //    var parameterName = frets.Item2;
        //    val = frets.Item3;
        //    //--------------------------------------------------------------------------------------------------------------
        //    #region Oracle
        //    if (direction == ParameterDirection.Output)
        //    {
        //        para = new Oracle.ManagedDataAccess.Client.OracleParameter()
        //        {
        //            ParameterName = parameterName,
        //            Value = val,
        //            Direction = direction
        //        };
        //        if (valDBType == DbType.Object)
        //        {
        //            para.Value = DBNull.Value;
        //            (para as Oracle.ManagedDataAccess.Client.OracleParameter).OracleDbType = Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor;
        //        }
        //        else para.DbType = valDBType;
        //        return para;
        //    }
        //    para = new Oracle.ManagedDataAccess.Client.OracleParameter()
        //    {
        //        ParameterName = parameterName,
        //        Value = val,
        //        Direction = ParameterDirection.Input
        //    };
        //    if (tb != null && tb.TableInfo != null)
        //    {
        //        var dtinfo = tb.TableInfo.Find(f => f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
        //        if (dtinfo != null)
        //        {
        //            try
        //            {
        //                (para as Oracle.ManagedDataAccess.Client.OracleParameter).OracleDbType = dtinfo.Type.Trim('2').ToEnum<Oracle.ManagedDataAccess.Client.OracleDbType>();
        //                if (dtinfo.MaxLength > 0 && dtinfo.MaxLength < int.MaxValue)
        //                    (para as Oracle.ManagedDataAccess.Client.OracleParameter).Size = (int)dtinfo.MaxLength;
        //            }
        //            catch { }
        //        }
        //    }
        //    #endregion
        //    return para;
        //}
        //public Dictionary<DbType, string> DbTypeToOracleMappings { get; } = new Dictionary<DbType, string>
        //{
        //    { DbType.Boolean, "NUMBER(1,0)"},
        //    { DbType.Int16, "INTEGER" },
        //    { DbType.Int32, "INTEGER" },
        //    { DbType.Int64, "INTEGER" },
        //    { DbType.Single, "FLOAT" },
        //    { DbType.Double, "FLOAT" },
        //    { DbType.Decimal, "FLOAT" },
        //    { DbType.VarNumeric, "NUMBER" },
        //    { DbType.Currency, "NUMBER" },
        //    { DbType.String, "NVARCHAR2" },
        //    { DbType.StringFixedLength, "NCHAR" },
        //    { DbType.AnsiString, "VARCHAR2" },
        //    { DbType.AnsiStringFixedLength, "CHAR" },
        //    { DbType.Date, "DATE" },
        //    { DbType.DateTime, "DATE" },
        //    { DbType.DateTime2, "TIMESTAMP" },
        //    { DbType.DateTimeOffset, "TIMESTAMP" },
        //    { DbType.Time, "DATE" },
        //    { DbType.Binary, "LONG RAW" },
        //    { DbType.Guid, "RAW(16)" },
        //    { DbType.Byte, "UNSIGNED INTEGER" },
        //    { DbType.SByte, "INTEGER" },
        //    { DbType.Object, "LONG RAW" },
        //    { DbType.Xml, "RAW" },
        //    { DbType.UInt16, "UNSIGNED INTEGER" },
        //    { DbType.UInt32, "UNSIGNED INTEGER" },
        //    { DbType.UInt64, "UNSIGNED INTEGER" },
        //};
        #endregion
        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public override List<Tuple<string, string, string>> MappingTypes
        {
            get
            {
                return new List<Tuple<string, string, string>>()
                    {
                        Tuple.Create("BFILE", "byte[]", "BFile"),
                        Tuple.Create("BLOB", "byte[]", "Blob"),
                        Tuple.Create("CHAR", "string", "Char"),
                        Tuple.Create("CLOB", "string", "Clob"),
                        Tuple.Create("DATE", "DateTime", "DateTime"),
                        Tuple.Create("TIMESTAMP", "DateTime", "DateTime"),
                        Tuple.Create("TIMESTAMP WITH LOCAL TIME ZONE", "DateTime", "DateTime"),
                        Tuple.Create("TIMESTAMP WITH TIME ZONE", "DateTime", "DateTime"),
                        Tuple.Create("LONG RAW", "byte[]", "LongRaw"),
                        Tuple.Create("LONG", "string", "LongVarChar"),
                        Tuple.Create("NCHAR", "string", "NChar"),
                        Tuple.Create("NCLOB", "string", "NClob"),
                        Tuple.Create("NUMBER", "decimal", "Number"),
                        Tuple.Create("INTEGER", "decimal", "Number"),
                        Tuple.Create("UNSIGNED INTEGER", "decimal", "Number"),
                        Tuple.Create("NVARCHAR2", "string", "NVarChar"),
                        Tuple.Create("RAW", "byte[]", "Raw"),
                        Tuple.Create("RAWID", "string", "RowId"),
                        Tuple.Create("VARCHAR2", "string", "VarChar"),
                        Tuple.Create("FLOAT", "decimal", "Float")
                    };
            }
        }

        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public override string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "blob";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public override string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "VarChar";
        }
        #endregion

        #region Sql Fun Templet
        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public override string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"NVL({check_expression},{replacement_value})";
        }
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public override string SqlDateNow
        {
            get
            {
                return " SYSDATE ";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public override string FullSqlDateNow
        {
            get
            {
                return "select sysdate from dual";
            }
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public override string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public override string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public override string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTR({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public override string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public override string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public override string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "INT");
        }

        public override string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "Number");
        }
        public override string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "Number(18,4)");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public override string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "Number(18, " + d + ")");
        }

        public override string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "BIT");
        }

        public override string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "VARCHAR2(4000)");
        }

        public override string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "UNIQUEIDENTIFIER");
        }

        public override string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "TIMESTAMP");
        }

        public override string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, " TRUNC({0},'dd')");
        }

        public override string SQL_ToTime(string column_name)
        {
            return string.Format(" to_timestamp({0},'0000-01-01 hh24:mi:ss') ", column_name);
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            var sqlMeta = @"SELECT " + fieldsstr + " FROM " + tbname + " WHERE {0} " + (top > 0 ? " AND ROWNUM <= " + top + " " : "") + orderbyStr;
            return sqlMeta;
        }

        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">当前页面</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public override string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums + 1;
            var n = page * nums;

            var sqlMeta = "SELECT /*+ FIRST_ROWS */ * FROM "
                            + $"(SELECT A.*, ROWNUM RN FROM(SELECT {fieldsstr} FROM {tbName} WHERE {whereStr } {orderbyStr}) A "
                           + $" WHERE ROWNUM <= {n} ) WHERE RN >= {m}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public override string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE INTO " + tbname + " a  USING(SELECT 1  from dual) source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2}";
            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public override string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public override string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public override string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"{sequenceName}.nextval";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public override string GetDataBaseSql
        {
            get
            {
                return @"SELECT t.username AS ""name"",t.user_id FROM ALL_USERS T";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public override string GetTableInfoListSql
        {
            get
            {
                return @"SELECT OWNER,TABLE_NAME AS ""name"", COMMENTS AS ""Description""
                          FROM ALL_TAB_COMMENTS
                         WHERE TABLE_TYPE= 'TABLE' AND OWNER = '{0}'";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public override string GetViewInfoListSql
        {
            get
            {
                return @"SELECT OWNER,TABLE_NAME AS ""name"", COMMENTS AS ""Description""
                          FROM ALL_TAB_COMMENTS
                         WHERE TABLE_TYPE= 'VIEW' AND OWNER = '{0}'";
            }
        }
        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public override string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"SELECT A.COLUMN_ID AS ""ColumnId"", A.COLUMN_NAME AS ""Name"",
                               A.DATA_TYPE AS ""Type"", A.DATA_LENGTH AS ""MaxLength"",
                               CASE
                                 WHEN A.NULLABLE = 'N' THEN
                                  0
                                 ELSE
                                  1
                               END AS ""IsNullable"", B.COMMENTS AS ""Description"",
                               CASE
                                 WHEN (SELECT COUNT(*)
                                         FROM ALL_CONS_COLUMNS C
                                        WHERE C.TABLE_NAME = A.TABLE_NAME
                                          AND C.COLUMN_NAME = A.COLUMN_NAME
                                          AND C.CONSTRAINT_NAME =
                                              (SELECT D.CONSTRAINT_NAME
                                                 FROM ALL_CONSTRAINTS D
                                                WHERE D.TABLE_NAME = C.TABLE_NAME AND ROWNUM<2
                                                  AND D.CONSTRAINT_TYPE = 'P')) > 0 THEN
                                  1
                                 ELSE
                                  0
                               END AS ""IsPrimaryKey""

                          FROM ALL_TAB_COLS A, ALL_COL_COMMENTS B
                         WHERE A.TABLE_NAME = B.TABLE_NAME
                           AND B.COLUMN_NAME = A.COLUMN_NAME 
                           AND A.OWNER=B.OWNER AND( A.OWNER='{0}' OR '{0}' IS NULL)
                           AND A.TABLE_NAME = '{1}'
                         ORDER BY A.TABLE_NAME, A.COLUMN_ID";
            }
        }
        #endregion

        #region DDL
        public override IDbFirst DbFirst
        {
            get { return new OracleDbFirst(this.dbHelper.DBName); }
        }
        #region DDL sql
        protected override string IsAnyIndexSql
        {
            get
            {
                return "select count(1) from user_ind_columns where index_name=('{0}')";
            }
        }
        protected override string CreateIndexSql
        {
            get
            {
                return "CREATE {3} INDEX Index_{0}_{2} ON {0}({1})";
            }
        }
        protected override string AddDefaultValueSql
        {
            get
            {
                return "ALTER TABLE {0} MODIFY({1} DEFAULT '{2}')";
            }
        }
        protected override string CreateDataBaseSql
        {
            get
            {
                return "CREATE DATABASE {0}";
            }
        }
        protected override string AddPrimaryKeySql
        {
            get
            {
                return "ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY({2})";
            }
        }
        protected override string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD ({1} {2}{3} {4} {5} {6})";
            }
        }
        protected override string AlterColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} modify ({1} {2}{3} {4} {5} {6}) ";
            }
        }
        protected override string BackupDataBaseSql
        {
            get
            {
                return @"USE master;BACKUP DATABASE {0} TO disk = '{1}'";
            }
        }
        protected override string CreateTableSql
        {
            get
            {
                return "CREATE TABLE {0}(\r\n{1})";
            }
        }
        protected override string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }
        protected override string TruncateTableSql
        {
            get
            {
                return "TRUNCATE TABLE {0}";
            }
        }
        protected override string BackupTableSql
        {
            get
            {
                return "create table {1} as select * from {2}  where ROWNUM<={0}";
            }
        }
        protected override string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }
        protected override string DropColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} DROP COLUMN {1}";
            }
        }
        protected override string DropConstraintSql
        {
            get
            {
                return "ALTER TABLE {0} DROP CONSTRAINT  {1}";
            }
        }
        protected override string RenameColumnSql
        {
            get
            {
                return "ALTER TABLE {0} rename   column  {1} to {2}";
            }
        }
        protected override string AddColumnRemarkSql
        {
            get
            {
                return "comment on column {1}.{0} is '{2}'";
            }
        }

        protected override string DeleteColumnRemarkSql
        {
            get
            {
                return "comment on column {1}.{0} is ''";
            }
        }

        protected override string IsAnyColumnRemarkSql
        {
            get
            {
                return "select * from user_col_comments where Table_Name='{1}' AND COLUMN_NAME='{0}' order by column_name";
            }
        }

        protected override string AddTableRemarkSql
        {
            get
            {
                return "comment on table {0}  is  '{1}'";
            }
        }

        protected override string DeleteTableRemarkSql
        {
            get
            {
                return "comment on table {0}  is  ''";
            }
        }

        protected override string IsAnyTableRemarkSql
        {
            get
            {
                return "select * from user_tab_comments where Table_Name='{0}'order by Table_Name";
            }
        }

        protected override string RenameTableSql
        {
            get
            {
                return "alter table {0} rename to {1}";
            }
        }
        #endregion

        #region Check
        protected override string CheckSystemTablePermissionsSql
        {
            get
            {
                return "select  t.table_name from user_tables t  where rownum=1";
            }
        }
        #endregion

        #region Scattered
        protected override string CreateTableNull
        {
            get
            {
                return "";
            }
        }
        protected override string CreateTableNotNull
        {
            get
            {
                return " NOT NULL ";
            }
        }
        protected override string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }
        protected override string CreateTableIdentity
        {
            get
            {
                return "";
            }
        }
        #endregion

        #region Methods
        public override bool AddColumn(string tableName, DbColumnInfo columnInfo)
        {
            if (columnInfo.Type == "varchar" && columnInfo.MaxLength == 0)
            {
                columnInfo.Type = "varchar2";
                columnInfo.MaxLength = 50;
            }
            return base.AddColumn(tableName, columnInfo);
        }
        public override bool CreateIndex(string tableName, string[] columnNames, bool isUnique = false)
        {
            string sql = string.Format(CreateIndexSql, tableName, string.Join(",", columnNames), string.Join("_", columnNames.Select(it => (it + "abc").Substring(0, 3))), isUnique ? "UNIQUE" : "");
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public override bool AddDefaultValue(string tableName, string columnName, string defaultValue)
        {
            if (defaultValue == "''")
            {
                defaultValue = "";
            }
            if (defaultValue.ToLower().IsIn("sysdate"))
            {
                var template = AddDefaultValueSql.Replace("'", "");
                string sql = string.Format(template, tableName, columnName, defaultValue);
                var sqle = this.dbHelper.CreateSqlEntity(sql);
                this.dbHelper.Execute(sqle);
                return true;
            }
            else
            {
                return base.AddDefaultValue(tableName, columnName, defaultValue);
            }
        }
        public override bool CreateDatabase(string databaseDirectory = null)
        {
            throw new NotSupportedException();
        }
        public override bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            throw new NotSupportedException();
        }
        public override bool AddRemark(DbTableInfo entity)
        {
            var columns = entity.Columns.Where(it => it.IsIgnore == false).ToList();

            foreach (var item in columns)
            {
                if (item.Description != null)
                {
                    //column remak
                    if (IsAnyColumnRemark(item.Name.ToUpper(), entity.TableName.ToUpper()))
                    {
                        DeleteColumnRemark(item.Name.ToUpper(), entity.TableName.ToUpper());
                        AddColumnRemark(item.Name.ToUpper(), entity.TableName.ToUpper(), item.Description);
                    }
                    else
                    {
                        AddColumnRemark(item.Name.ToUpper(), entity.TableName.ToUpper(), item.Description);
                    }
                }
            }

            //table remak
            if (entity.Description != null)
            {
                if (IsAnyTableRemark(entity.TableName))
                {
                    DeleteTableRemark(entity.TableName);
                    AddTableRemark(entity.TableName, entity.Description);
                }
                else
                {
                    AddTableRemark(entity.TableName, entity.Description);
                }
            }
            return true;
        }

        public override bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            if (columns.HasValue())
            {
                foreach (var item in columns)
                {
                    if (item.Name.Equals("GUID", StringComparison.CurrentCultureIgnoreCase) && item.MaxLength == 0)
                    {
                        item.MaxLength = 50;
                    }
                    if (item.Type == "varchar" && item.MaxLength == 0)
                    {
                        item.MaxLength = 50;
                    }
                }
            }
            string sql = GetCreateTableSql(tableName, columns);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            if (isCreatePrimaryKey)
            {
                var pkColumns = columns.Where(it => it.IsPrimaryKey).ToList();
                foreach (var item in pkColumns)
                {
                    AddPrimaryKey(tableName, item.Name);
                }
            }
            return true;
        }
        #endregion
        #endregion

        #region ExecutePro
        public override SqlEntity GetSqlEntityPro(SqlEntity sql, Dictionary<string, object> paras)
        {
            if (sql.Sql.IsNullOrEmpty_()) return sql;
            sql.CommandType = CommandType.StoredProcedure;
            sql.Sql = sql.Sql.ToUpper();

            if (!dicDbParameter.TryGetValue(sql.Sql, out Dictionary<string, CNDbParameter> dbparas))
            {
                dbparas = new Dictionary<string, CNDbParameter>(StringComparer.OrdinalIgnoreCase);
                var dstype = dbHelper.QueryDataTable(new SqlEntity(dbHelper) { Sql = $"select t.object_name,t.argument_name,t.data_type,t.in_out from all_arguments t where t.object_name='{sql.Sql.ToUpper()}' order by t.POSITION", CommandType = CommandType.Text });
                if (dstype != null && dstype.Rows.Count > 0)
                {
                    foreach (DataRow dr in dstype.Rows)
                    {
                        var dbp = new CNDbParameter();
                        dbp.ParameterName = dr.GetColumnValue("ARGUMENT_NAME").NullToStr().Replace(":", "");
                        dbp.TypeName = dr.GetColumnValue("DATA_TYPE").NullToStr();
                        var in_out = dr.GetColumnValue("IN_OUT").NullToStr();
                        dbp.Direction = in_out == "IN" ? System.Data.ParameterDirection.Input : System.Data.ParameterDirection.Output;
                        dbparas[dbp.ParameterName] = dbp;
                    }
                }
                dicDbParameter[sql.Sql] = dbparas;
            }
            if (dbparas?.Count > 0)
            {
                var param = new List<object>();
                //foreach (var dbp in dbparas.Values)
                //{
                //    if (dbp.TypeName == "REF CURSOR")
                //    {
                //        param.Add(dbp.ParameterName, null, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
                //    }
                //    else
                //    {
                //        if (!paras.TryGetValue(dbp.ParameterName, out object val))
                //            val = null;
                //        param.Add(dbp.ParameterName, val, dbp.TypeName.ToEnumOrNull_<Oracle.ManagedDataAccess.Client.OracleDbType>(), dbp.Direction);
                //    }
                //}
                sql.AddParameters(param);
            }
            return sql;
        }
        #endregion
    }
}
